	package browser.service;

	import browser.dao.UserInfo;
	import java.io.*;
	import java.sql.*;
	import java.util.ArrayList;
	import java.util.Arrays;
	import java.util.HashMap;
	import java.util.Iterator;
	import java.util.Map;
	import javax.servlet.*;
	import javax.servlet.http.*;
	import org.json.JSONArray;
	import org.json.JSONException;
	import org.json.JSONObject;

	/**
	 * Servlet that will execute procedures and functions.
	 * It will send output results back to client in Json format.
	 */
	public class Execute extends HttpServlet {

		public static Connection conn;
		public static Statement stmt;

		public void doGet(HttpServletRequest request,
				HttpServletResponse response) throws ServletException,
				IOException {

			// doPost(request,response);
			response.setContentType("text/html");
			PrintWriter out = response.getWriter();

			// Check if session is expired
			if (request.getSession(false) == null
					|| (Boolean) request.getSession(false).getAttribute(
							"expired") == true) {
				out.println("Session expired");
				return;
			}

			String[] uid = request.getParameterValues("uid");
			String[] uacc = request.getParameterValues("uAccess");
			String[] objname = request.getParameterValues("object_name");
			ArrayList<String> params = new ArrayList<String>();
			HashMap<String, String> in_params = new HashMap<String, String>();
			HashMap<String, String> out_params = new HashMap<String, String>();

			String[] queryString = request.getQueryString().split("&");
			if (queryString.length > 3) {
				for (int i = 3; i < queryString.length; i++) {
					params.add(queryString[i]);
				}
			}
			for (int i = 0; i < params.size(); i++) {
				if (params.get(i).indexOf("=") == -1) {
					out_params.put(params.get(i), null);
				} else {
					if (params.get(i).split("=").length == 2) {
						in_params.put(params.get(i).split("=")[0],
								java.net.URLDecoder.decode(params.get(i).split(
										"=")[1], "UTF-8"));
					} else {
						in_params.put(params.get(i).split("=")[0], "");
					}
				}
			}

			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
			} catch (ClassNotFoundException e) {
				out.println(e.getMessage());
				return;
			}

			Connection conn = null;
			UserInfo user = MyUtilities.getUserInfo(uid[0]);

			if ((user.getUaccess() == null)
					|| (!uacc[0].equals(user.getUaccess()))) {
				return;
			}
			try {
				conn = DriverManager.getConnection("jdbc:oracle:thin:"
						+ user.getUid() + "/" + user.getPassword()
						+ "@oraserver.cs.luc.edu:1521:cs");
			} catch (SQLException e) {
				out.println(e.getMessage());
				return;
			}
			if (conn == null) {
				return;
			}
			Statement stmt = null;
			try {
				stmt = conn.createStatement();
				ResultSet rset = null;
				String query = "select argument_name,data_type,in_out from user_arguments where object_name='"
						+ objname[0] + "' order by position";
				rset = stmt.executeQuery(query);

				String call = "";
				if (out_params.containsKey("null")) {
					call = "{? = call " + objname[0] + "(";
				} else {
					call = "{call " + objname[0] + "(";
				}

				while (rset.next()) {
					if (rset.getString("argument_name") == null)
						continue;
					if (rset.getString("data_type").equals("NUMBER")
							&& rset.getString("in_out").equals("IN")) {
						call += "to_number(?),";
					} else if (rset.getString("data_type").equals("DATE")
							&& rset.getString("in_out").equals("IN")) {
						call += "to_date(?,'YYYY-MM-DD HH24:MI:SS'),";
					} else {
						call += "?,";
					}
				}
				if (!call.contains(",")) {
					call += ")}";
				} else {
					call = call.substring(0, call.lastIndexOf(',')) + ")}";
				}
				rset = stmt.executeQuery(query);

				CallableStatement callstmt = conn.prepareCall(call);

				int index = 1;
				while (rset.next()) {
					if (rset.getString("in_out").equals("IN")) {
						callstmt.setString(index, in_params.get(rset
								.getString("argument_name")));
					} else if (rset.getString("in_out").equals("OUT")) {
						callstmt.registerOutParameter(index, Types.VARCHAR);
						out_params.put(rset.getString("argument_name") == null ? "null"
												: rset.getString("argument_name"),
										String.valueOf(index));
					} else if (rset.getString("in_out").equals("IN/OUT")) {
						callstmt.setString(index, in_params.get(rset.getString("argument_name")));
						callstmt.registerOutParameter(index, Types.VARCHAR);
						out_params.put(rset.getString("argument_name"), String.valueOf(index));
					}
					index++;
				}

				callstmt.execute();

				// JSONObject json = new JSONObject();
				// JSONArray json_params = new JSONArray();
				JSONObject json_param = new JSONObject();

				Iterator it = out_params.entrySet().iterator();
				while (it.hasNext()) {
					Map.Entry<String, String> entry = (Map.Entry<String, String>) it
							.next();
					json_param.put(entry.getKey(), callstmt.getString(Integer
							.parseInt(entry.getValue())));

				}
				// json_params.put(json_param);
				out.println(json_param.toString());

			} catch (SQLException e) {
				try {
					conn.close();
				} catch (SQLException e2) {
				}
				;
				e.printStackTrace();
				out.println(e.getMessage());
				return;
			} catch (JSONException e) {
				out.println(e.getMessage());
				e.printStackTrace();
			}

			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			out.close();
		}

		public void doPost(HttpServletRequest request,
				HttpServletResponse response) throws ServletException,
				IOException {
			doGet(request, response);
		}

	}
